# -*- coding: utf-8 -*-
__author__ = 'yangq'

from appcommon.appinstance import mysql_app_pool
from tornado import gen
from common import timetool


@gen.coroutine
def get_menu_tree():
    sql_parent = (
        "select menu_id, menu_name from menu_tree where parent_id = -1 "
        "and is_using = 1 "
        "order by sort_id;"
    )
    menu_parent = yield mysql_app_pool.get_all(sql_parent, cursor='dict')

    sql_link = (
        "select menu_id, menu_name, link, parent_id from menu_tree where parent_id <> -1 "
        "and is_using = 1 "
        "and power = 0 "
        "order by sort_id;"
    )
    menu_link = yield mysql_app_pool.get_all(sql_link, cursor='dict')
    res = []
    for p in menu_parent:
        tmp = dict(
            menu_name=p['menu_name'],
            link_list=[]
        )
        for l in menu_link:
            if l['parent_id'] == p['menu_id']:
                tmp['link_list'].append(dict(
                    link_name=l['menu_name'],
                    link=l['link']
                ))
        res.append(tmp)
    raise gen.Return(res)


@gen.coroutine
def get_manage_menu(menu_type, parent_id, left):
    if menu_type == '0':
        sql = (
            "select menu_id, menu_name, sort_id, link, power from menu_tree where parent_id = -1 "
            "and is_using = 1 "
            "order by sort_id;"
        )
        tmp = yield mysql_app_pool.get_all(sql, cursor='dict')
        if left:
            res = dict(
                code='0',
                info=tmp
            )
        else:
            res = dict(
                total=len(tmp),
                rows=tmp
            )
    else:
        sql = (
            "select menu_id, menu_name, link, power, sort_id from menu_tree where parent_id <> -1 "
            "and is_using = 1 "
            "and parent_id = {} "
            "order by sort_id;"
        )
        tmp = yield mysql_app_pool.get_all(sql.format(parent_id), cursor='dict')
        res = dict(
            total=len(tmp),
            rows=tmp
        )
    raise gen.Return(res)


@gen.coroutine
def update_manage_menu(args):
    print args['menu_name']
    if args['menu_id']:
        sql = (
            "update menu_tree set menu_name = '{menu_name}', link = '{link}', power='{power}', sort_id='{sort_id}' where "
            "is_using = 1 and menu_id = '{menu_id}'"
        )
        yield mysql_app_pool.update(sql.format(**args))
    else:
        sql = (
            "insert into menu_tree (menu_name, link, parent_id, power, sort_id) "
            "values ('{menu_name}','{link}',{parent_id},{power},{sort_id})"
        )
        yield mysql_app_pool.update(sql.format(**args))
    res = 0
    raise gen.Return(res)


@gen.coroutine
def del_manage_menu(menu_id):
    print menu_id
    sql = (
        "delete from menu_tree where menu_id = {} "
    )
    res = yield mysql_app_pool.update(sql.format(menu_id))
    raise gen.Return(res)


@gen.coroutine
def check_phone_is_vip(phone, current_time):
    is_vip = "0"
    vip_end = ""
    if phone:
        sql = (
            u"select end from app_vip_user where phone='{}'"
        )
        end = yield mysql_app_pool.get_one(sql.format(phone, current_time, current_time))
        if end:
            is_vip = "1" if end[0] > current_time else "0"
            vip_end = end[0]

    res = dict(
        is_vip=is_vip,
        vip_end=vip_end
    )
    raise gen.Return(res)


@gen.coroutine
def check_phone_is_vip4(phone, current_time):
    is_vip = "0"
    vip_end = ""
    if phone:
        sql = (
            u"select end from app_vip4_user where phone='{}'"
        )
        end = yield mysql_app_pool.get_one(sql.format(phone, current_time, current_time))
        if end:
            is_vip = "1" if end[0] > current_time else "0"
            vip_end = end[0]

    res = dict(
        is_vip=is_vip,
        vip_end=vip_end
    )
    raise gen.Return(res)


@gen.coroutine
def check_phone_is_bmt(phone, current_time):
    is_vip = "0"
    vip_end = ""
    if phone:
        sql = (
            u"select end from couch_bmt where phone='{}'"
        )
        end = yield mysql_app_pool.get_one(sql.format(phone, current_time, current_time))
        if end:
            is_vip = "1" if end[0] > current_time else "0"
            vip_end = end[0]

    res = dict(
        is_vip=is_vip,
        vip_end=vip_end
    )
    raise gen.Return(res)


@gen.coroutine
def check_phone_is_couch(phone):
    if phone:
        sql = (
            u"select 1 from app_couch_user where phone='{}'"
        )
        is_couch = yield mysql_app_pool.get_one(sql.format(phone))
        res = "1" if is_couch else "0"
    else:
        res = "0"
    raise gen.Return(res)


@gen.coroutine
def del_couch_user(phone):
    sql = (
        u"delete from app_couch_user where phone='{}'"
    )
    res = yield mysql_app_pool.delete(sql.format(phone))
    raise gen.Return(res)


@gen.coroutine
def del_vip_user(phone):
    sql = (
        u"delete from app_vip_user where phone='{}'"
    )
    res = yield mysql_app_pool.delete(sql.format(phone))
    raise gen.Return(res)


@gen.coroutine
def del_vip4_user(phone):
    sql = (
        u"delete from app_vip4_user where phone='{}'"
    )
    res = yield mysql_app_pool.delete(sql.format(phone))
    raise gen.Return(res)


@gen.coroutine
def del_bmt_user(phone):
    sql = (
        u"delete from couch_bmt where phone='{}'"
    )
    res = yield mysql_app_pool.delete(sql.format(phone))
    raise gen.Return(res)


@gen.coroutine
def add_vip_user(phone, start, end):
    sql = (
        u"insert into app_vip_user (phone, start, end) values ('{}', {}, {})"
    )
    res = yield mysql_app_pool.insert_one(sql.format(phone, start, end))
    raise gen.Return(res)


@gen.coroutine
def add_vip4_user(phone, start, end):
    sql = (
        u"insert into app_vip4_user (phone, start, end) values ('{}', {}, {})"
    )
    res = yield mysql_app_pool.insert_one(sql.format(phone, start, end))
    raise gen.Return(res)


@gen.coroutine
def add_bmt_user(phone, start, end):
    sql = (
        u"insert into couch_bmt (phone, start, end) values ('{}', {}, {})"
    )
    res = yield mysql_app_pool.insert_one(sql.format(phone, start, end))
    raise gen.Return(res)


@gen.coroutine
def get_vip_user_list():
    current_time = timetool.get_timestamp()
    sql = (
        u"select a.phone, a.nick_name, a.avatar, "
        "b.phone as is_vip, "
        "b.end as vip_end "
        "from app_user_info a LEFT JOIN app_vip_user b on a.phone = b.phone "
        "and b.end > {} "
        "order by a.id desc"
    )
    res = yield mysql_app_pool.get_all(sql.format(current_time), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_vip4_user_list():
    current_time = timetool.get_timestamp()
    sql = (
        u"select a.phone, a.nick_name, a.avatar, "
        "b.phone as is_vip, "
        "b.end as vip_end "
        "from app_user_info a LEFT JOIN app_vip4_user b on a.phone = b.phone "
        "and b.end > {} "
        "order by a.id desc"
    )
    res = yield mysql_app_pool.get_all(sql.format(current_time), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_bmt_user_list():
    current_time = timetool.get_timestamp()
    sql = (
        u"select a.phone, a.nick_name, a.avatar, "
        "b.phone as is_vip, "
        "b.end as vip_end "
        "from app_user_info a LEFT JOIN couch_bmt b on a.phone = b.phone "
        "and b.end > {} "
        "order by a.id desc"
    )
    res = yield mysql_app_pool.get_all(sql.format(current_time), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_couch_user_list(phone):
    param = "where c.phone='{}'".format(phone) if phone else ""
    sql = (
        u"select a.nick_name, a.avatar, "
        "c.* "
        "from couch_auth c LEFT JOIN app_user_info a "
        "on a.phone= c.phone "
        "{} "
        "order by a.id desc"
    )
    res = yield mysql_app_pool.get_all(sql.format(param), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_user_info_with_phone(phone):
    sql = (
        u"select a.phone, a.nick_name, a.avatar "
        "from app_user_info a where a.phone = '{}'"
    )
    res = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_score_list(phone):
    param = "and a.phone = '{}'".format(phone) if phone else ""
    sql = (
        "select a.phone, a.nick_name, a.avatar, IFNULL(b.score, 0) as score from app_user_info a LEFT JOIN app_user_score b "
        "on a.phone = b.phone "
        "where 1=1 "
        "{}"
        "order by a.id desc "
    )
    res = yield mysql_app_pool.get_all(sql.format(param), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_score_detail(phone):
    sql = (
        "select * from app_score_log where phone = '{}' "
        "order by pubtime desc"
    )
    res = yield mysql_app_pool.get_all(sql.format(phone), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_price_list():
    sql = (
        "select * from order_price "
        "order by id desc"
    )
    res = yield mysql_app_pool.get_all(sql, cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def update_price(order_type, price, youhui_price):
    sql = (
        "update order_price set price={}, youhui_price={} where order_type = '{}' "
    )
    res = yield mysql_app_pool.update(sql.format(price, youhui_price, order_type))
    raise gen.Return(res)


@gen.coroutine
def update_score_2_mysql(phone, action, score):
    sql = (
        u"select * from app_user_score where phone='{}'"
    )
    old_score = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    if old_score:
        if action == 'add':
            news_score = old_score['score'] + score
        else:
            news_score = old_score['score'] - score
        sql = (
            u"update app_user_score set score = {} where phone='{}' "
        )
        yield mysql_app_pool.update(sql.format(news_score, phone))
    else:
        if action == 'add':
            news_score = score
        else:
            news_score = 0 - score
        sql = (
            u"insert into app_user_score (phone, score) values ('{}', {})"
        )
        yield mysql_app_pool.update(sql.format(phone, news_score))
    raise gen.Return(news_score)


@gen.coroutine
def save_score_log(phone, score, type, pubtime, desc):
    sql = (
        "insert into app_score_log (phone, score, `type`, pubtime, `desc`) values "
        u"('{}', {}, {}, {}, '{}')"
    )
    yield mysql_app_pool.insert_one(sql.format(phone, score, type, pubtime, desc))
    raise gen.Return(0)


@gen.coroutine
def get_couch_auth(phone):
    sql = (
        "select * from couch_auth where phone = '{}'"
    )
    res = yield mysql_app_pool.get_one(sql.format(phone), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def set_couch_auth(phone, ztype, status):
    ziduan = ztype + '_status'
    sql = (
        "update couch_auth set {} = {} where phone = '{}'"
    )
    res = yield mysql_app_pool.update(sql.format(ziduan, status, phone))
    raise gen.Return(res)


def check_couch_auth_cnt(info):
    cnt = 0
    if info:
        if info['jlz_status'] == 1:
            cnt += 1
        if info['sfz_status'] == 1:
            cnt += 1
        if info['jsz_status'] == 1:
            cnt += 1
        if info['xsz_status'] == 1:
            cnt += 1
    return cnt


@gen.coroutine
def set_couch_auth_status(phone, status):
    sql = (
        "update couch_auth set status = {} where phone = '{}'"
    )
    res = yield mysql_app_pool.update(sql.format(status, phone))
    raise gen.Return(res)


@gen.coroutine
def get_all_user_info():
    sql = (
        "select a.phone, a.nick_name, a.avatar, a.area, b.score as money, c.area as school_area, c.school, d.end as vip ,"
        "e.status, f.sex, f.tel, f.area as couch_area, f.school as couch_school, "
        "f.years, f.car_type, f.score, g.end as bmt "
        "from app_user_info a  "
        "LEFT JOIN app_user_score b "
        "on a.phone=b.phone "
        "LEFT JOIN user_school c "
        "on a.phone=c.phone "
        "LEFT JOIN app_vip_user d "
        "on a.phone=d.phone "
        "LEFT JOIN couch_auth e "
        "on a.phone=e.phone "
        "LEFT JOIN couch_base_info f "
        "on a.phone=f.phone "
        "LEFT JOIN couch_bmt g "
        "on a.phone=g.phone"
    )
    res = yield mysql_app_pool.get_all(sql, cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_base_cnt():
    sql = (
        "select * from app_base_cnt"
    )
    res = yield mysql_app_pool.get_all(sql, cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def update_base_cnt(vip_base_cnt, student_base_cnt):
    sql = (
        "update app_base_cnt set vip_base_cnt={}, student_base_cnt={}"
    )
    res = yield mysql_app_pool.update(sql.format(vip_base_cnt, student_base_cnt))
    raise gen.Return(res)


@gen.coroutine
def get_couch_score_list(phone):
    param = "and a.phone = '{}'".format(phone) if phone else ""
    sql = (
        "select a.phone, a.nick_name, a.avatar, IFNULL(b.score, 0) as score from app_user_info a LEFT JOIN couch_base_info b "
        "on a.phone = b.phone "
        "where 1=1 "
        "{}"
        "order by a.id desc "
    )
    res = yield mysql_app_pool.get_all(sql.format(param), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_couch_score_detail(phone):
    sql = (
        "select * from couch_score_log where phone = '{}' "
        "order by pubtime desc"
    )
    res = yield mysql_app_pool.get_all(sql.format(phone), cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def get_score_conf():
    sql = (
        "select * from app_score_conf "
        "order by id desc"
    )
    res = yield mysql_app_pool.get_all(sql, cursor='dict')
    raise gen.Return(res)


@gen.coroutine
def set_score_conf(user_type, source, score):
    sql = (
        "update app_score_conf set score={} where user_type = '{}' and source='{}'"
    )
    res = yield mysql_app_pool.update(sql.format(score, user_type, source))
    raise gen.Return(res)